package com.chinamobile.maps.util;

import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.chinamobile.maps.orm.Channelnetworks;

public class ImportUtils {
	public static List<Channelnetworks> importChannelNetworksFromExcel(
			String path) throws IOException {
		List list = new ArrayList();
		Workbook workbook = new HSSFWorkbook(new FileInputStream(path));
		Sheet sheet = workbook.getSheetAt(0);
		Iterator iter = sheet.iterator();

		if (iter.hasNext()) {
			iter.next();
		}
		while (iter.hasNext()) {
			Row row = (Row) iter.next();
			Cell cell = row.getCell(0);
			if (cell == null)
				break;
			list.add(writeToChannelNetwork(row));
		}

		return list;
	}

	private static Channelnetworks writeToChannelNetwork(Row row) {
		Channelnetworks channelNetwork = new Channelnetworks();
		channelNetwork.setSn(getCellString(row.getCell(0), true));
		channelNetwork.setRegionName(getCellString(row.getCell(1)));
		channelNetwork.setRegionProperty(getCellString(row.getCell(2)));
		channelNetwork.setChannelCode(getCellString(row.getCell(3), true));
		channelNetwork.setMaketingUnit(getCellString(row.getCell(4)));
		channelNetwork.setMicroRegion(getCellString(row.getCell(5)));
		channelNetwork.setChannelType(getCellString(row.getCell(6)));
		channelNetwork.setNetworkType(getCellString(row.getCell(7)));
		channelNetwork.setName(getCellString(row.getCell(8)));
		channelNetwork.setOutsourcing(getCellString(row.getCell(9)));
		channelNetwork.setAddress(getCellString(row.getCell(10)));
		channelNetwork.setManager(getCellString(row.getCell(11)));
		channelNetwork.setPhone(getCellString(row.getCell(12), true));

		String longitudeStr = getCellString(row.getCell(13), false);
		if ((!StringUtils.isBlank(longitudeStr))
				&& (StringUtils.isNumeric(StringUtils.replace(longitudeStr,
						".", "")))) {
			channelNetwork.setLongitude(new BigDecimal(longitudeStr));
		}
		String latitudeStr = getCellString(row.getCell(14), false);
		if ((!StringUtils.isBlank(latitudeStr))
				&& (StringUtils.isNumeric(StringUtils.replace(latitudeStr, ".",
						"")))) {
			channelNetwork.setLatitude(new BigDecimal(latitudeStr));
		}
		channelNetwork.setSpecialty(getCellString(row.getCell(15)));
		channelNetwork.setRegionEnvironment(getCellString(row.getCell(16)));
		channelNetwork.setArea(getCellString(row.getCell(17), true));

		return channelNetwork;
	}

	private static String getCellString(Cell cell) {
		return getCellString(cell, false);
	}

	private static String getCellString(Cell cell, boolean toInt) {
		if (cell != null) {
			switch (cell.getCellType()) {
			case 3:
				return cell.getStringCellValue();
			case 1:
				return cell.getRichStringCellValue().getString();
			case 0:
				if (toInt) {
					return MessageFormat.format("{0,number,#}",
							new Object[] { Double.valueOf(cell
									.getNumericCellValue()) });
				}
				return String.valueOf(cell.getNumericCellValue());
			case 2:
				return cell.getRichStringCellValue().getString();
			}
			return "";
		}
		return null;
	}
}